﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

namespace DAO
{
    public class DebtDAO
    {
        public static DataTable BaoCaoDuNoLon()
        {
            SqlConnection connect = null;
            DataTable rs = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }


                rs = SqlHelper.ExecuteDataset(connect, CommandType.StoredProcedure, "usp_TongDuNoCaNhan").Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                if (rs != null)
                    ((IDisposable)rs).Dispose();

                if (connect != null)
                    connect.Dispose();
            }

            return rs;
        }
        public static DataTable LayKhoanVay(String makh, int loaikh)
        {
            SqlConnection connect = null;
            DataTable rs = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SqlParameter[] param = new SqlParameter[2];
                param[0] = new SqlParameter("@makh", makh);
                param[1] = new SqlParameter("@loaikh", loaikh);
                rs = SqlHelper.ExecuteDataset(connect, CommandType.StoredProcedure, "usp_LayKhoanVay", param).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                if (rs != null)
                    ((IDisposable)rs).Dispose();

                if (connect != null)
                    connect.Dispose();
            }

            return rs;
        }

        public static int CapNhatKhoanVay(Entity.KhoanVay kv)
        {
            SqlConnection connect = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }
  
                SqlParameter[] param = new SqlParameter[14];
                param[0] = new SqlParameter("@makhoanvay", kv.MaKhoanVay);
                param[1] = new SqlParameter("@mucdichvay", kv.MucDichVay);
                param[2] = new SqlParameter("@ngayvay", kv.NgayVay);
                param[3] = new SqlParameter("@kyhan", kv.KyHan);
                param[4] = new SqlParameter("@ngaytra", kv.NgayTra);
                param[5] = new SqlParameter("@laisuat", kv.LaiSuat);
                param[6] = new SqlParameter("@BDTV", kv.BDTV);
                param[7] = new SqlParameter("@xeploai", kv.XepLoai);
                param[8] = new SqlParameter("@giatrikhoanvay", kv.GiaTri);
                param[9] = new SqlParameter("@thuno", kv.ThuNo);
                param[10] = new SqlParameter("@duno", kv.DuNo);
                param[11] = new SqlParameter("@nguontrano", kv.NguonTraNo);
                param[12] = new SqlParameter("@makhcanhan", kv.MaKhachHangCN);
                param[13] = new SqlParameter("@makhdoanhnghiep", kv.MaKhachHangDN);

                return SqlHelper.ExecuteNonQuery(connect, CommandType.StoredProcedure, "usp_CapNhatKhoanVay", param);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                if (connect != null)
                    connect.Dispose();
            }
        }

        public static int ThemMoiKhoanVay(Entity.KhoanVay kv)
        {
            SqlConnection connect = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SqlParameter[] param = new SqlParameter[13];
                param[1] = new SqlParameter("@mucdichvay", kv.MucDichVay);
                param[2] = new SqlParameter("@ngayvay", kv.NgayVay);
                param[3] = new SqlParameter("@kyhan", kv.KyHan);
                param[4] = new SqlParameter("@ngaytra", kv.NgayTra);
                param[5] = new SqlParameter("@laisuat", kv.LaiSuat);
                param[6] = new SqlParameter("@BDTV", kv.BDTV);
                param[7] = new SqlParameter("@xeploai", kv.XepLoai);
                param[8] = new SqlParameter("@giatrikhoanvay", kv.GiaTri);
                param[9] = new SqlParameter("@thuno", kv.ThuNo);
                param[10] = new SqlParameter("@duno", kv.DuNo);
                param[11] = new SqlParameter("@nguontrano", kv.NguonTraNo);
                param[12] = new SqlParameter("@makhcanhan", kv.MaKhachHangCN);
                param[13] = new SqlParameter("@makhdoanhnghiep", kv.MaKhachHangDN);

                return SqlHelper.ExecuteNonQuery(connect, CommandType.StoredProcedure, "usp_ThemKhoanVay", param);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                if (connect != null)
                    connect.Dispose();
            }
        }


        public static int XoaKhoanVay(int makv)
        {
            SqlConnection connect = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SqlParameter[] param = new SqlParameter[1];
                param[0] = new SqlParameter("@makv", makv);
                return SqlHelper.ExecuteNonQuery(connect, CommandType.StoredProcedure, "usp_XoaKhoanVay", param);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                if (connect != null)
                    connect.Dispose();
            }

        }

        public static DataTable LayKhoanVayTheoMaKH(String makh, int loaikh)
        {
            SqlConnection connect = null;
            DataTable rs = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SqlParameter[] param = new SqlParameter[2];
                param[0] = new SqlParameter("@makh", makh);
                param[1] = new SqlParameter("@loaikh", loaikh);
                rs = SqlHelper.ExecuteDataset(connect, CommandType.StoredProcedure, "usp_LayKhoanVayTheoMaKH", param).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                if (rs != null)
                    ((IDisposable)rs).Dispose();

                if (connect != null)
                    connect.Dispose();
            }

            return rs;
        }

        public static DataTable LayKhoanVayDenHan()
        {
            SqlConnection connect = null;
            DataTable rs = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

               
                rs = SqlHelper.ExecuteDataset(connect, CommandType.StoredProcedure, "usp_KhoanVayDenHanAll").Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                if (rs != null)
                    ((IDisposable)rs).Dispose();

                if (connect != null)
                    connect.Dispose();
            }

            return rs;
        }

        public static DataTable LayKhoanVayDenHanTheoMaNV(int manv)
        {
            SqlConnection connect = null;
            DataTable rs = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SqlParameter[] param = new SqlParameter[1];
                param[0] = new SqlParameter("@manv", manv);
                rs = SqlHelper.ExecuteDataset(connect, CommandType.StoredProcedure, "usp_KhoanVayDenHanTheoMaNV", param).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                if (rs != null)
                    ((IDisposable)rs).Dispose();

                if (connect != null)
                    connect.Dispose();
            }

            return rs;
        }
    }
      

}
